package com.teamsun.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.PropertyFilter;
import com.teamsun.annotation.PrivateHandlerMethod;
import com.teamsun.cache.CacheManager;
import com.teamsun.entity.DBCfg;
import com.teamsun.entity.TableHeader;
import com.teamsun.entity.UserInfo;
import com.teamsun.rptcfg.BaseRpt;
import com.teamsun.rptcfg.ParseRptCfg;
import com.teamsun.service.IRptMgrService;
import com.teamsun.service.ISaveRptService;
import com.teamsun.service.ISequenceService;
import com.teamsun.system.ISysService;
import com.teamsun.util.Constants;
import com.teamsun.util.ReadExcelHeader;
import com.teamsun.util.RptCfgUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>报表管理Controller</p>
 */
@Controller
@RequestMapping("/rptTools/rptMgr")
public class RptMgrController {

    @Autowired
    private IRptMgrService rptMgrService;

    @Autowired
    private ISaveRptService saveRptService;

    @Autowired
    private ISequenceService sequenceService;

    @Autowired
    private CacheManager cacheManager;

    /**
     * 数据源管理页面跳转
     * @return
     */
    @RequestMapping("/dbCfg")
    @PrivateHandlerMethod
    public String dbCfg() {
        return "../WEB-INF/page/dbCfg";
    }

    /**
     * 配置说明页面跳转
     * @return
     */
    @RequestMapping("/doc")
    @PrivateHandlerMethod
    public String doc() {
        return "../WEB-INF/page/doc";
    }

    /**
     * 使用手册页面跳转
     * @return
     */
    @RequestMapping("/manual")
    @PrivateHandlerMethod
    public String manual() {
        return "../WEB-INF/page/manual";
    }

    /**
     * 报表管理页面跳转
     * @return
     */
    @RequestMapping("/rptIndex")
    @PrivateHandlerMethod
    public String rptIndex() {
        return "../WEB-INF/page/rptIndex";
    }

    /**
     * 新增报表页面跳转
     * @return
     */
    @RequestMapping("/addRpt")
    @PrivateHandlerMethod
    public String addRpt() {
        return "../WEB-INF/page/addRpt";
    }

    /**
     * 新增报表页面跳转
     * @return
     */
    @RequestMapping("/editRpt")
    @PrivateHandlerMethod
    public String editRpt(HttpServletRequest request) throws Exception {
        String rptName = request.getParameter("rptName");
        rptName = URLDecoder.decode(rptName, "UTF-8");

        String dbName = request.getParameter("dbName");
        dbName = URLDecoder.decode(dbName, "UTF-8");

        String rptId = request.getParameter("rptId");
        String sysCode = request.getParameter("sysCode");

        request.setAttribute("encodeName", rptName);
        request.setAttribute("encodedb", dbName);
        request.setAttribute("rptId", rptId);
        request.setAttribute("sysCode", sysCode);
        return "../WEB-INF/page/editRpt";
    }

    /**
     * 查询报表数据
     */
    @RequestMapping("/queryRptInfo")
    @ResponseBody
    @PrivateHandlerMethod
    public JSONObject queryRptInfo(HttpServletRequest request, int page, int rows) {
        JSONObject jsonObject = new JSONObject();

        /**
         * 报表配置数据
         */
        int startIndex = (page - 1) * rows + 1;
        int endIndex = page * rows;

        Map<String, Object> param = new HashMap<String, Object>();
        UserInfo userInfo = (UserInfo) request.getSession().getAttribute(Constants.SESSION_USER);
        String rptName = request.getParameter("rptName");
        String[] dbValues = request.getParameterValues("dbValues[]");
        String[] accessIns = request.getParameterValues("accessIns[]");
        // 管理员可以查看所有报表
        String isAdmin = userInfo.getAdmin();
        if(!Constants.YES.equals(isAdmin)) {
            param.put("createUser", userInfo.getUserId());
        }
        if(StringUtils.isNotBlank(rptName)) {
            param.put("rptName", rptName);
        }
        if(dbValues != null && dbValues.length > 0) {
            param.put("dataSource", dbValues);
        }
        if(accessIns != null && accessIns.length > 0) {
            param.put("accessIn", accessIns);
        }
        Map<String, Object> resultMap = rptMgrService.queryRptInfoByPage(param, startIndex, endIndex);

        jsonObject.put("rows", resultMap.get("rows"));
        jsonObject.put("total", resultMap.get("total"));
        return jsonObject;
    }

    /**
     * 刷新缓存
     */
    @RequestMapping("/refreshCache")
    @ResponseBody
    public JSONObject refreshCache(HttpServletRequest request) {
        JSONObject jsonObject = new JSONObject();

        System.out.println("刷新缓存...");
        // 获取报表配置文件的保存路径
        String rptCfgFilePath = (String) cacheManager.getCacheValue(Constants.RPTCFGPATH);
        // 获取报表表头文件的保存路径
        String rptHeaderFilePath = (String) cacheManager.getCacheValue(Constants.RPTHEADERPATH);
        //String rptCfgFilePath = request.getSession().getServletContext().getRealPath("/WEB-INF/rptConfig");

        try {
            List<BaseRpt> baseRpts = saveRptService.queryAllRptCfg();
            if (baseRpts != null && baseRpts.size() > 0) {
                for (BaseRpt baseRpt : baseRpts) {
                    int rptId = baseRpt.getRptId();
                    // 报表配置文件名
                    String fileName = "RPT_" + rptId + ".xml";
                    File rptCfgFile = new File(rptCfgFilePath + "/" + fileName);
                    InputStream inputStream = new FileInputStream(rptCfgFile);

                    // 解析报表配置文件
                    ParseRptCfg parseRptCfg = new ParseRptCfg();
                    BaseRpt rptInfo = parseRptCfg.parseRptCfg(inputStream, fileName);
                    rptInfo.setRptId(baseRpt.getRptId());
                    rptInfo.setRptName(baseRpt.getRptName());
                    rptInfo.setDataSource(baseRpt.getDataSource());
                    rptInfo.setRptToken(baseRpt.getRptToken());
                    rptInfo.setAccessSysCode(baseRpt.getAccessSysCode());
                    //rptInfo.setRptHeader(baseRpt.getRptHeader());

                    // 重置表头信息(补充表头信息中field、align、width等字段的值)
                    String rptHeaderJson = baseRpt.getRptHeader();
                    List<List<TableHeader>> tableHeaders = RptCfgUtils.resetRptHeader(rptHeaderJson, rptInfo);
                    PropertyFilter filter = new PropertyFilter() {
                        @Override
                        public boolean apply(Object object, String name, Object value) {
                            if((name.equals("sortable") && "false".equals(String.valueOf(value))) || ((name.equals("hidden") && "false".equals(String.valueOf(value)))) || (name.equals("colspan") && "0".equals(String.valueOf(value))) || ((name.equals("rowspan") && "0".equals(String.valueOf(value)))) || ((name.equals("width") && "0".equals(String.valueOf(value))))) {
                                return false;
                            }
                            return true;
                        }
                    };
                    rptHeaderJson = JSON.toJSONString(tableHeaders, filter);
                    rptInfo.setRptHeader(rptHeaderJson);
                    // 获取Excel表头信息
                    ReadExcelHeader readExcelHeader = new ReadExcelHeader();
                    String excelFileName = "RPT_" + rptId + ".xlsx";
                    InputStream excelInputStream = new FileInputStream(rptHeaderFilePath + "/" + excelFileName);
                    List<String> titleList = readExcelHeader.getXlsxExcelHeaderInfo(excelInputStream, tableHeaders.size());
                    rptInfo.setExcelHeader(titleList);

                    // 缓存报表配置数据
                    cacheManager.addRptCache(String.valueOf(rptId), rptInfo);
                }

                jsonObject.put("status", "1");
            }
        } catch (Exception e) {
            System.out.print("读取报表配置文件失败");
            jsonObject.put("status", "0");
            e.printStackTrace();
        }
        return jsonObject;
    }

    /**
     * 下载报表配置文件
     */
    @RequestMapping("/downLoadRptCfg")
    @ResponseBody
    @PrivateHandlerMethod
    public void downLoadRptCfg(HttpServletRequest request, HttpServletResponse response) {
        try {
            String rptId = request.getParameter("rptId");  // 报表ID
            String rptName = request.getParameter("rptName");
            rptName = URLDecoder.decode(rptName, "UTF-8");
            rptName = rptName + "_" + rptId + ".xml";
            String rptCfgFileName = "RPT_" + rptId + ".xml";  // 报表配置文件名
            String rptCfgFilePath = (String) cacheManager.getCacheValue(Constants.RPTCFGPATH);
            InputStream inputStream = new FileInputStream(rptCfgFilePath + "/" + rptCfgFileName);
            //InputStream inputStream = request.getSession().getServletContext().getResourceAsStream("/WEB-INF/rptConfig/" + rptCfgFileName);

            byte[] bytes = new byte[1024];
            int len = 0;
            OutputStream load = response.getOutputStream();
            response.setBufferSize(512);
            response.setContentType("application/vnd.ms-excel");
            response.addHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(rptName, "UTF-8"));
            while ((len = inputStream.read(bytes)) > 0) {
                load.write(bytes, 0, len);
            }
            load.flush();
            load.close();
            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 下载报表表头Excel配置文件
     */
    @RequestMapping("/downLoadRptHeader")
    @ResponseBody
    @PrivateHandlerMethod
    public void downLoadRptHeader(HttpServletRequest request, HttpServletResponse response) {
        try {
            String rptId = request.getParameter("rptId");  // 报表ID
            String rptName = request.getParameter("rptName");
            rptName = URLDecoder.decode(rptName, "UTF-8");
            rptName = rptName + "_" + rptId + ".xlsx";
            String rptCfgFileName = "RPT_" + rptId + ".xlsx";  // 报表配置文件名
            String rptCfgFilePath = (String) cacheManager.getCacheValue(Constants.RPTHEADERPATH);
            InputStream inputStream = new FileInputStream(rptCfgFilePath + "/" + rptCfgFileName);
            //InputStream inputStream = request.getSession().getServletContext().getResourceAsStream("/WEB-INF/rptConfig/" + rptCfgFileName);

            byte[] bytes = new byte[1024];
            int len = 0;
            OutputStream load = response.getOutputStream();
            response.setBufferSize(512);
            response.setContentType("application/vnd.ms-excel");
            response.addHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(rptName, "UTF-8"));
            while ((len = inputStream.read(bytes)) > 0) {
                load.write(bytes, 0, len);
            }
            load.flush();
            load.close();
            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除报表
     */
    @RequestMapping("/deleteRpt")
    @ResponseBody
    @PrivateHandlerMethod
    public JSONObject deleteRpt(HttpServletRequest request, HttpServletResponse response) {
        JSONObject jsonObject = new JSONObject();

        try {
            String rptId = request.getParameter("rptId");  // 报表ID
            String rptCfgFileName = "RPT_" + rptId + ".xml";  // 报表配置文件名
            String rptHeaderXlsx = "RPT_" + rptId + ".xlsx";  // 报表表头文件名(xlsx格式)
            String rptHeaderXls = "RPT_" + rptId + ".xls";  // 报表表头文件名(xls格式)
            String rptCfgFilePath = (String) cacheManager.getCacheValue(Constants.RPTCFGPATH);  // 报表配置文件路径
            String rptHeaderPath = (String) cacheManager.getCacheValue(Constants.RPTHEADERPATH); // 报表表头excel文件路径
            rptCfgFilePath = rptCfgFilePath + "/" + rptCfgFileName;
            String rptHeaderXlsxPath = rptHeaderPath + "/" + rptHeaderXlsx;
            String rptHeaderXlsPath = rptHeaderPath + "/" + rptHeaderXls;

            // 删除数据库中的报表配置信息
            Map<String, Object> param = new HashMap<String, Object>();
            param.put("rptId", rptId);
            rptMgrService.deleteRptInfo(param);

            // 删除报表配置文件
            File rptCfgFile = new File(rptCfgFilePath);
            if(rptCfgFile.exists()) {
                rptCfgFile.delete();
            }

            File rptHeaderXlsxFile = new File(rptHeaderXlsxPath);
            if(rptHeaderXlsxFile.exists()) {
                rptHeaderXlsxFile.delete();
            }
            File rptHeaderXlsFile = new File(rptHeaderXlsPath);
            if(rptHeaderXlsFile.exists()) {
                rptHeaderXlsFile.delete();
            }

            jsonObject.put("status", "1");
        } catch (Exception e) {
            jsonObject.put("status", "0");
            e.printStackTrace();
        }

        return jsonObject;
    }

    /**
     * 刷新指定报表ID的缓存
     * @param request
     * @param response
     * @return
     */
    @RequestMapping("/refreshCacheById")
    @ResponseBody
    public JSONObject refreshRptById(HttpServletRequest request, HttpServletResponse response) {
        JSONObject jsonObject = new JSONObject();

        System.out.println("刷新缓存...");
        String rptId = request.getParameter("rptId");
        // 获取报表配置文件的保存路径
        String rptCfgFilePath = (String) cacheManager.getCacheValue(Constants.RPTCFGPATH);
        // 获取报表表头文件的保存路径
        String rptHeaderFilePath = (String) cacheManager.getCacheValue(Constants.RPTHEADERPATH);
        //String rptCfgFilePath = request.getSession().getServletContext().getRealPath("/WEB-INF/rptConfig");

        try {
            BaseRpt baseRpt = saveRptService.queryRptCfgById(rptId);
            if (baseRpt != null) {
                // 报表配置文件名
                String fileName = "RPT_" + rptId + ".xml";
                File rptCfgFile = new File(rptCfgFilePath + "/" + fileName);
                InputStream inputStream = new FileInputStream(rptCfgFile);

                // 解析报表配置文件
                ParseRptCfg parseRptCfg = new ParseRptCfg();
                BaseRpt rptInfo = parseRptCfg.parseRptCfg(inputStream, fileName);
                rptInfo.setRptId(baseRpt.getRptId());
                rptInfo.setRptName(baseRpt.getRptName());
                rptInfo.setDataSource(baseRpt.getDataSource());
                rptInfo.setRptToken(baseRpt.getRptToken());
                rptInfo.setAccessSysCode(baseRpt.getAccessSysCode());
                //rptInfo.setRptHeader(baseRpt.getRptHeader());

                // 重置表头信息(补充表头信息中field、align、width等字段的值)
                String rptHeaderJson = baseRpt.getRptHeader();
                List<List<TableHeader>> tableHeaders = RptCfgUtils.resetRptHeader(rptHeaderJson, rptInfo);
                PropertyFilter filter = new PropertyFilter() {
                    @Override
                    public boolean apply(Object object, String name, Object value) {
                        if((name.equals("sortable") && "false".equals(String.valueOf(value))) || ((name.equals("hidden") && "false".equals(String.valueOf(value)))) || (name.equals("colspan") && "0".equals(String.valueOf(value))) || ((name.equals("rowspan") && "0".equals(String.valueOf(value)))) || ((name.equals("width") && "0".equals(String.valueOf(value))))) {
                            return false;
                        }
                        return true;
                    }
                };
                rptHeaderJson = JSON.toJSONString(tableHeaders, filter);
                rptInfo.setRptHeader(rptHeaderJson);
                // 获取Excel表头信息
                ReadExcelHeader readExcelHeader = new ReadExcelHeader();
                String excelFileName = "RPT_" + rptId + ".xlsx";
                InputStream excelInputStream = new FileInputStream(rptHeaderFilePath + "/" + excelFileName);
                List<String> titleList = readExcelHeader.getXlsxExcelHeaderInfo(excelInputStream, tableHeaders.size());
                rptInfo.setExcelHeader(titleList);

                // 缓存报表配置数据
                cacheManager.addRptCache(String.valueOf(rptId), rptInfo);

                jsonObject.put("status", "1");
            }
        } catch (Exception e) {
            System.out.print("读取报表配置文件失败");
            jsonObject.put("status", "0");
            e.printStackTrace();
        }
        return jsonObject;
    }

    /**
     * 查询数据库配置信息
     */
    @RequestMapping("/queryDBCfgInfo")
    @ResponseBody
    @PrivateHandlerMethod
    public JSONObject queryDBCfgInfo(HttpServletRequest request, int page, int rows) {
        JSONObject jsonObject = new JSONObject();

        /**
         * 报表配置数据
         */
        int startIndex = (page - 1) * rows + 1;
        int endIndex = page * rows;

        Map<String, Object> param = new HashMap<String, Object>();
        String dbName = request.getParameter("dbName");
        if(StringUtils.isNotBlank(dbName)) {
            dbName = dbName.trim();
            param.put("dbName", dbName);
        }

        Map<String, Object> resultMap = rptMgrService.queryDBCfgByPage(param, startIndex, endIndex);

        jsonObject.put("rows", resultMap.get("rows"));
        jsonObject.put("total", resultMap.get("total"));
        return jsonObject;
    }

    /**
     * 删除数据库配置信息
     */
    @RequestMapping("/deleteDBCfg")
    @ResponseBody
    @PrivateHandlerMethod
    public JSONObject deleteDBCfg(HttpServletRequest request, HttpServletResponse response) {
        JSONObject jsonObject = new JSONObject();

        try {
            String dbId = request.getParameter("dbId");  // 数据库ID
            String dbName = request.getParameter("dbName");  // 数据库名称

            // 查询数据库是否被报表使用，如果有报表使用该数据库则不能删除
            List<Map<String, Object>> rptInfos = rptMgrService.queryRptByDBId(dbId);
            if(rptInfos != null && rptInfos.size() > 0) {
                jsonObject.put("status", "0");
                jsonObject.put("errorMsg", "该数据库正在被报表使用,无法删除");
            } else {
                // 删除数据库中的报表配置信息
                Map<String, Object> param = new HashMap<String, Object>();
                param.put("dbId", dbId);
                param.put("dbName", dbName);
                rptMgrService.deleteDBCfg(param);

                jsonObject.put("status", "1");
            }
        } catch (Exception e) {
            jsonObject.put("status", "0");
            jsonObject.put("errorMsg", "删除数据库配置失败");
            e.printStackTrace();
        }

        return jsonObject;
    }

    /**
     *  新增数据库配置信息
     */
    @RequestMapping("/addDBCfg")
    @ResponseBody
    @PrivateHandlerMethod
    public JSONObject addDBCfg(HttpServletRequest request) {
        JSONObject jsonObject = new JSONObject();
        String errroMsg = "";
        try {
            String dbName = request.getParameter("dbName");  // 数据库名称
            dbName = dbName.trim();
            String dbType = request.getParameter("dbType");  // 数据库类型
            String dbURL = request.getParameter("dbURL");  // 数据库连接
            String dbDesc = request.getParameter("dbDesc");  // 数据库描述
            String dbUser = request.getParameter("dbUser");  // 数据库用户名
            String dbPwsd = request.getParameter("dbPwsd");  // 数据库密码
            String dbDriver = "";
            if(Constants.DBTYPE_ORACLE.equals(dbType)) {
                dbDriver = Constants.ORACLE_DRIVER;
            } else if(Constants.DBTYPE_MYSQL.equals(dbType)) {
                dbDriver = Constants.MYSQL_DRIVER;
            }
            // 获取ID
            int dbId = sequenceService.getRptSequence();

            // 查看数据名称是否重复
            List<DBCfg> dbCfgs = rptMgrService.queryDBCfgByDBName(dbName);
            if(dbCfgs != null && dbCfgs.size() > 0) {
                jsonObject.put("status", "0");
                jsonObject.put("errorMsg", "数据库名称[" + dbName + "]已经存在");
            } else {
                // 将报表配置信息保存至数据库
                DBCfg dbCfg = new DBCfg();
                dbCfg.setSid(dbId);
                dbCfg.setDbName(dbName);
                dbCfg.setDbType(dbType);
                dbCfg.setDriverClass(dbDriver);
                dbCfg.setJdbcURL(dbURL);
                dbCfg.setUserName(dbUser);
                dbCfg.setPassword(dbPwsd);
                if(StringUtils.isNotBlank(dbDesc)) {
                    dbCfg.setDbCommment(dbDesc);
                }
                rptMgrService.addDBCfg(dbCfg);
                jsonObject.put("status", "1");
            }
        } catch (Exception e) {
            errroMsg = "新增数据库配置失败";
            jsonObject.put("status", "0");
            jsonObject.put("errorMsg", errroMsg);
            e.printStackTrace();
        }
        return jsonObject;
    }

    /**
     * 修改数据库配置信息
     * @param request
     * @return
     */
    @RequestMapping("/updateDBCfg")
    @ResponseBody
    @PrivateHandlerMethod
    public JSONObject updateDBCfg(HttpServletRequest request) {
        JSONObject jsonObject = new JSONObject();
        String errroMsg = "";
        try {
            String dbId = request.getParameter("dbId");
            String dbName = request.getParameter("dbName");  // 数据库名称
            dbName = dbName.trim();
            String dbType = request.getParameter("dbType");  // 数据库类型
            String dbURL = request.getParameter("dbURL");  // 数据库连接
            String dbDesc = request.getParameter("dbDesc");  // 数据库描述
            String dbUser = request.getParameter("dbUser");  // 数据库用户名
            String dbPwsd = request.getParameter("dbPwsd");  // 数据库密码
            String dbDriver = "";
            if(Constants.DBTYPE_ORACLE.equals(dbType)) {
                dbDriver = Constants.ORACLE_DRIVER;
            } else if(Constants.DBTYPE_MYSQL.equals(dbType)) {
                dbDriver = Constants.MYSQL_DRIVER;
            }

            boolean editFlag = true;
            // 查看数据名称是否重复
            List<DBCfg> dbCfgs = rptMgrService.queryDBCfgByDBName(dbName);
            if(dbCfgs != null && dbCfgs.size() > 0) {
                DBCfg dbCfg = dbCfgs.get(0);
                String sid = String.valueOf(dbCfg.getSid());
                if(!sid.equals(dbId)) {
                    editFlag = false;
                    jsonObject.put("status", "0");
                    jsonObject.put("errorMsg", "数据库名称[" + dbName + "]已经存在");
                }
            }

            if(editFlag) {
                // 将报表配置信息保存至数据库
                DBCfg dbCfg = new DBCfg();
                dbCfg.setSid(Integer.valueOf(dbId));
                dbCfg.setDbName(dbName);
                dbCfg.setDbType(dbType);
                dbCfg.setDriverClass(dbDriver);
                dbCfg.setJdbcURL(dbURL);
                dbCfg.setUserName(dbUser);
                dbCfg.setPassword(dbPwsd);
                if(StringUtils.isNotBlank(dbDesc)) {
                    dbCfg.setDbCommment(dbDesc);
                } else {
                    dbCfg.setDbCommment(" ");
                }
                rptMgrService.updateDBCfg(dbCfg);
                jsonObject.put("status", "1");
            }
        } catch (Exception e) {
            errroMsg = "修改数据库配置失败";
            jsonObject.put("status", "0");
            jsonObject.put("errorMsg", errroMsg);
            e.printStackTrace();
        }
        return jsonObject;
    }
}
